USE tempdb;
GO

SET NOCOUNT ON;
GO

PRINT 'configuration of tempdb files'
SELECT
	af.filename AS physical_name,
	af.name AS logical_name,
	df.type_desc,
	df.state_desc,
	CEILING(af.size * 8 / 1024.0) AS initial_size_mb,
	CEILING(df.size * 8 / 1024.0) AS current_size_mb,
	auto_growth =
		CASE df.max_size
			WHEN 0 THEN 'Off'
			WHEN -1 THEN 'On'
			ELSE 'Log file will grow to a maximum size of 2 TB.'
		END,
		CASE WHEN is_percent_growth = 1 THEN CAST(df.growth AS varchar(10))  + '%' 
	ELSE
		CAST(ROUND(df.growth * 8 /1024,0) AS varchar(20)) 
	END AS growth_value,
	CASE df.is_percent_growth
		WHEN 1 THEN 'PERCENT'
		WHEN 0 THEN 'MB'
		ELSE 'UNKNOWN'
	END AS growth_type,
	CASE WHEN df.max_size = -1 OR df.max_size = 268435456 THEN 'UNLIMITED'
		WHEN df.max_size = 0 THEN 'NO_GROWTH' 
		WHEN df.max_size <> -1 OR df.max_size <> 0 THEN CAST(((df.max_size * 8) / 1024) AS varchar(15))
		ELSE 'UNKNOWN'
	END AS maxfile_size_mb
FROM master.sys.sysaltfiles af WITH (NOLOCK)
INNER JOIN tempdb.sys.database_files df WITH (NOLOCK) ON af.fileid = df.file_id
WHERE af.dbid = DB_ID('tempdb') OPTION (RECOMPILE);

PRINT 'tempdb space used by user and by internal objects'
SELECT
    ROUND(CONVERT(FLOAT, SUM(user_object_reserved_page_count)) * 8.0 / 1024, 2) AS user_objects_mb,
    ROUND(CONVERT(FLOAT, SUM(internal_object_reserved_page_count) * 8.0 / 1024), 2) AS internal_objects_mb,
    ROUND(CONVERT(FLOAT, SUM(version_store_reserved_page_count) * 8.0 / 1024), 2) AS version_store_mb,
    ROUND(CONVERT(FLOAT, SUM(unallocated_extent_page_count) * 8.0 / 1024), 2) AS freespace_mb
FROM sys.dm_db_file_space_usage WITH (NOLOCK) OPTION (RECOMPILE);

PRINT 'top 10 executing tasks and tempdb space usage'
SELECT TOP 10
	t1.session_id, 
	t1.request_id, 
	t1.task_alloc_page_count,
	t1.task_alloc_pages_mb,
    t1.task_dealloc_page_count,
	t1.task_dealloc_pages_mb,
		(SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
			  (CASE WHEN statement_end_offset = -1 
				  THEN LEN(CONVERT(NVARCHAR(MAX),text)) * 2 
					   ELSE statement_end_offset 
				  END - t2.statement_start_offset)/2)
		 FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
	 (SELECT query_plan FROM sys.dm_exec_query_plan(t2.plan_handle)) AS query_plan
	FROM (SELECT session_id, request_id,
	SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) AS task_alloc_page_count,
	ROUND(CONVERT(FLOAT, SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) * 8.0 / 1024), 2) AS task_alloc_pages_mb,
	SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS task_dealloc_page_count,
	ROUND(CONVERT(FLOAT, SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) * 8.0 / 1024), 2) AS task_dealloc_pages_mb
		   FROM sys.dm_db_task_space_usage WITH (NOLOCK)
		   GROUP BY session_id, request_id) AS t1, 
		   sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id
	AND	(t1.request_id = t2.request_id)
	AND t1.session_id > 50
	AND t1.session_id <> @@SPID
ORDER BY t1.task_alloc_page_count DESC OPTION (RECOMPILE);

-- create a tempdb file for each cpu
/*
USE master;
GO

DECLARE
	@cpu_count      int,
    @file_count     int,
    @logical_name   sysname,
    @file_name      nvarchar(520),
    @physical_name  nvarchar(520),
    @size           int,
    @max_size       int,
    @growth         int,
    @alter_command  nvarchar(MAX);

SELECT
	@physical_name = physical_name,
    @size = size / 128, 
    @max_size = max_size / 128,
    @growth = growth / 128
FROM tempdb.sys.database_files
WHERE name = 'tempdev';

SELECT
	@file_count = COUNT(*)
FROM tempdb.sys.database_files
WHERE type_desc = 'ROWS';

SELECT
	@cpu_count = cpu_count
FROM sys.dm_os_sys_info;

WHILE @file_count < @cpu_count -- add * 0.25 here to add 1 file for every 4 cpus, * .5 for every 2 cpus etc.
 BEGIN
    SELECT @logical_name = 'tempdev' + CAST(@file_count AS nvarchar);
    SELECT @file_name = REPLACE(@physical_name, 'tempdb.mdf', @logical_name + '.ndf');
    SELECT @alter_command = 'ALTER DATABASE [tempdb] ADD FILE ( NAME =N''' + @logical_name + ''', FILENAME =N''' +  @file_name + ''', SIZE = ' + CAST(@size AS nvarchar) + 'MB, MAXSIZE = ' + CAST(@max_size AS nvarchar) + 'MB, FILEGROWTH = ' + CAST(@growth AS nvarchar) + 'MB )';
    PRINT @alter_command;
    EXECUTE sp_executesql @alter_command;
    SELECT @file_count += 1;
 END
 */